package com.corpgovernment.organization.service.impl;

import java.util.*;
import java.util.stream.Collectors;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.corpgovernment.common.apollo.BasicManageApollo;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.corpgovernment.common.apollo.BasicManageApollo;
import com.corpgovernment.common.base.JSONResult;
import com.corpgovernment.common.base.Page;
import com.corpgovernment.common.common.CorpBusinessException;
import com.corpgovernment.common.enums.ExceptionCodeEnum;
import com.corpgovernment.common.utils.DateUtil;
import com.corpgovernment.organization.constant.OrganizationResponseCodeEnum;
import com.corpgovernment.organization.dataloader.BasicOssClientLoader;
import com.corpgovernment.organization.entity.db.MbImportExcelRecord;
import com.corpgovernment.organization.entity.db.MbImportExcelRecordDetailLog;
import com.corpgovernment.organization.entity.db.MbOrgEmployeeInfo;
import com.corpgovernment.organization.enumm.MbImportExcelRecordStatusEnum;
import com.corpgovernment.organization.enumm.MbImportExcelRecordTypeEnum;
import com.corpgovernment.organization.enumm.ServiceErrorEnum;
import com.corpgovernment.organization.mapper.MbImportExcelRecordDetailLogMapper;
import com.corpgovernment.organization.mapper.MbImportExcelRecordMapper;
import com.corpgovernment.organization.model.org.vo.SearchMbImportExcelRecordDetailLogResponseVo;
import com.corpgovernment.organization.model.org.vo.SearchMbImportExcelRecordRequestVo;
import com.corpgovernment.organization.model.org.vo.SearchMbImportExcelRecordResponseVo;
import com.corpgovernment.organization.service.IMbImportExcelRecordService;
import com.ctrip.corp.obt.generic.utils.CollectionUtils;
import com.ctrip.corp.obt.generic.utils.JsonUtils;
import com.ctrip.corp.obt.generic.utils.StringUtils;
import com.ctrip.corp.obt.shard.context.PageContext;
import com.ctrip.corp.obt.shard.sql.mybatis.interceptor.pagination.IPage;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Service;
import tk.mybatis.mapper.entity.Example;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.HttpURLConnection;
import java.net.InetSocketAddress;
import java.net.Proxy;
import java.net.URL;
import java.util.*;
import java.util.stream.Collectors;

@Service
@Slf4j
public class MbImportExcelRecordServiceImpl implements IMbImportExcelRecordService {
    @Autowired
    private MbImportExcelRecordMapper mbImportExcelRecordMapper;
    @Autowired
    private MbImportExcelRecordDetailLogMapper mbImportExcelRecordDetailLogMapper;
    @Autowired
    private BasicOssClientLoader basicOssClientLoader;
    @Autowired
    OrganizationEmployeeServiceImpl organizationEmployeeService;
    @Autowired
    private BasicManageApollo basicServerApollo;

    @Override
    public Long insertRecord(MbImportExcelRecord mbImportExcelRecord) {
        if (mbImportExcelRecord == null) {
            return null;
        }
        mbImportExcelRecordMapper.insertSelective(mbImportExcelRecord);
        return mbImportExcelRecord.getId();
    }

    @Override
    public Boolean updateRecord(MbImportExcelRecord mbImportExcelRecord) {
        if (mbImportExcelRecord == null) {
            return true;
        }
        return mbImportExcelRecordMapper.updateByPrimaryKeySelective(mbImportExcelRecord) > 0;
    }

    @Override
    public MbImportExcelRecord getRecordById(Long recordId)  {
        return mbImportExcelRecordMapper.selectByPrimaryKey(recordId);
    }

    @Override
    public void batchInsertRecordDetailLog(List<MbImportExcelRecordDetailLog> mbImportExcelRecordDetailLogs)  {
        if (CollectionUtils.isEmpty(mbImportExcelRecordDetailLogs)) {
            return;
        }
        mbImportExcelRecordDetailLogMapper.insertList(mbImportExcelRecordDetailLogs);
    }

    @Override
    public Long insertRecordDetailLog(MbImportExcelRecordDetailLog mbImportExcelRecordDetailLog)  {
        if (mbImportExcelRecordDetailLog == null) {
            return null;
        }
        try {
            mbImportExcelRecordDetailLogMapper.insertSelective(mbImportExcelRecordDetailLog);
        } catch (Exception e){
            log.error("", e);
        }
        return mbImportExcelRecordDetailLog.getId();
    }

    /**
     * 分页查询导入记录
     *
     * @param requestVo
     * @param corpId
     * @return
     */
    @Override
    public JSONResult<Page> searchImportExcelRecordByPage(SearchMbImportExcelRecordRequestVo requestVo, String corpId) {
        List<SearchMbImportExcelRecordResponseVo> recordResponseVos = new ArrayList<>();
        //根据上传人名称查询uid
        Page<SearchMbImportExcelRecordResponseVo> page = new Page<>();
        try {
            List<String> uidList = null;
            if (!StringUtils.isBlank(requestVo.getOperatorName())) {
                List<MbOrgEmployeeInfo> mbOrgEmployeeInfos = organizationEmployeeService.listByNameAllPlat(requestVo.getOperatorName());
                uidList = Optional.ofNullable(mbOrgEmployeeInfos).orElse(new ArrayList<>()).stream().map(x -> x.getUid()).distinct().collect(Collectors.toList());
            }
            List<String> finalUidList = uidList;
            IPage<MbImportExcelRecord> iPage = PageContext.startPage(requestVo.getPageNum(), requestVo.getPageSize()
                    , () -> mbImportExcelRecordMapper.searchImportExcelRecordByPage(finalUidList, corpId, requestVo.getFileName(), requestVo.getType()));
            
            log.info("iPage：{}", JsonUtils.toJsonString(iPage));
            
            List<String> recordUidList = iPage.getRecords().stream().map(MbImportExcelRecord::getUid).distinct().collect(Collectors.toList());

            List<MbOrgEmployeeInfo> employeeInfos = organizationEmployeeService.listByUidsAllPlat(recordUidList);

            Map<String, String> nameMap = Optional.ofNullable(employeeInfos).orElse(new ArrayList<>()).stream().filter(Objects::nonNull).filter(x -> !StringUtils.isEmpty(x.getName()))
                    .collect(Collectors.toMap(MbOrgEmployeeInfo::getUid, MbOrgEmployeeInfo::getName, (k1, k2) -> k1));

            recordResponseVos = iPage.getRecords().stream().map(x -> convertSearchRecordVo(x, nameMap)).collect(Collectors.toList());

            page.setList(CollectionUtils.isEmpty(recordResponseVos) ? Collections.emptyList() : recordResponseVos);
            page.setPageNum((int) iPage.getCurrent());
            page.setPageSize((int) iPage.getSize());
            page.setTotalPage((int) iPage.getPages());
            page.setCount(iPage.getTotal());
        } catch (Exception e) {
            log.error("searchImportExcelRecordByPage", e);
            return JSONResult.errorMsg(ServiceErrorEnum.SearchImportExcelRecordByPage_Error.getErrorMessage());
        }
        return JSONResult.success(page);
    }

    @Override
    public JSONResult<Page> searchImportExcelRecordDetailLogByPage(Integer pageNum, Integer pageSize, Long recordId) {
        Page page = new Page();
        if (pageNum < 0 || pageSize < 0) {
            return JSONResult.success(page);
        }
        try {
            Example example = new Example(MbImportExcelRecordDetailLog.class);
            example.createCriteria().andEqualTo("recordId", recordId);
            example.setOrderByClause("line_no+0");
            IPage<MbImportExcelRecordDetailLog> iPage = PageContext.startPage(pageNum, pageSize);
            mbImportExcelRecordDetailLogMapper.selectByExample(example);
            page.setCount(iPage.getTotal());
            page.setPageNum((int) iPage.getCurrent());
            page.setPageSize((int) iPage.getSize());
            page.setTotalPage((int) iPage.getPages());
            page.setList(Optional.ofNullable(iPage.getRecords().stream().map(this::convertSearchRecordVo).collect(Collectors.toList())).orElse(Collections.emptyList()));
        } catch (Exception e) {
            log.error("searchImportExcelRecordDetailLogByPage", e);
            return JSONResult.errorMsg(ServiceErrorEnum.SearchImportExcelRecordDetailLogByPage_Error.getErrorMessage());
        }
        return JSONResult.success(page);
    }

    @Override
    public void downloadImportExcel(Long recordId, HttpServletResponse response) {
        try {
            MbImportExcelRecord mbImportExcelRecord = this.getRecordById(recordId);
            if(ObjectUtil.isNull(mbImportExcelRecord.getFileUrl())){
                throw new CorpBusinessException(OrganizationResponseCodeEnum.FILE_IS_NULL);
            }
            Example example = new Example(MbImportExcelRecordDetailLog.class);
            example.createCriteria().andEqualTo("recordId", recordId);
            example.setOrderByClause("line_no+0");
            List<MbImportExcelRecordDetailLog>  list = mbImportExcelRecordDetailLogMapper.selectByExample(example);
            // k--> sheetIndex+lineNo, v --> errorList
            Map<String, List<String>> mapMap = new HashMap<>();
            for (int i = 0; i < list.size(); i++) {
                MbImportExcelRecordDetailLog item = list.get(i);
                List<String> errorLogList = new ArrayList<>();
                String key = item.getSheetIndex()+"_"+ item.getLineNo();
                if(mapMap.containsKey(key)){
                    errorLogList =  mapMap.get(key);
                }
                errorLogList.add(item.getMessage());
                mapMap.put(key,errorLogList);
            }



            String ossUrl = basicOssClientLoader.privateGeneratePresignedUrl(mbImportExcelRecord.getFileUrl());

            // 填充excel-error错误信息
            fillExcelWithErrorStyle(response, mbImportExcelRecord, mapMap, ossUrl);
        } catch (Exception e) {
            log.error("searchImportExcelRecordDetailLogByPage", e);
        }

    }

    private void fillExcelWithErrorStyle(HttpServletResponse response, MbImportExcelRecord mbImportExcelRecord, Map<String, List<String>> mapMap, String ossUrl) {
        URL urlfile = null;
        HttpURLConnection httpUrl = null;
        try {
            if (ossUrl == null) {
                throw new CorpBusinessException(OrganizationResponseCodeEnum.FILE_IS_NULL);
            }
            urlfile = new URL(ossUrl);
            if(ObjectUtil.isNotEmpty(basicServerApollo.getProxyUrl())&&ObjectUtil.isNotEmpty(basicServerApollo.getProxyPort())){
                Proxy proxy = new Proxy(Proxy.Type.HTTP, new InetSocketAddress(basicServerApollo.getProxyUrl(), basicServerApollo.getProxyPort()));
                httpUrl = (HttpURLConnection) urlfile.openConnection(proxy);
            }else {
                httpUrl = (HttpURLConnection) urlfile.openConnection();
            }
            httpUrl.connect();
            XSSFWorkbook workBook = new XSSFWorkbook(httpUrl.getInputStream());


            for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
                XSSFSheet sheet = workBook.getSheetAt(i);
                Iterator<Row> rowIterator = sheet.iterator();
                Row row;
                int counter = 0;
                int cellIndex = 0;    // 从多少列添加错误提示
                int errorIndex = 0;   // 从多少行开始错误提示
                //para cada fila de la hoja
                while(rowIterator.hasNext()){
                    row = rowIterator.next();
                    // 取不同sheet 最后一列
                    if(i == 0){
                        cellIndex = 18;
                        errorIndex = 1;
                    }
                    if(i == 1){
                        cellIndex = 5;
                        errorIndex = 0;
                    }
                    XSSFCellStyle cellStyle = workBook.createCellStyle();
                    //单元格锁定的样式
                    cellStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                    cellStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                    Font font = workBook.createFont();
                    font.setColor(IndexedColors.RED.getIndex());
                    cellStyle.setFont(font);
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    if(counter == errorIndex ){
                        Cell cell = row.createCell(row.getLastCellNum());

                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue("错误提示");
                        cell.setCellStyle(cellStyle);
                    }
                    if(counter > errorIndex  ){
                        String key = i+"_"+(counter+1);
                        List<String> errorLogList =  mapMap.get(key);
                        if(CollectionUtil.isEmpty(errorLogList)){
                            counter++;
                            continue;
                        }
                        String errorLog = errorLogList.stream().collect(Collectors.joining(","));

                        Cell cell = row.createCell(cellIndex);
                        cell.setCellValue(errorLog);
                        cell.setCellStyle(cellStyle);

                        XSSFCellStyle itemStyle = workBook.createCellStyle();
                        //单元格锁定的样式
                        itemStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());
                        itemStyle.setFillBackgroundColor(IndexedColors.GOLD.getIndex());
                        itemStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            Cell cellItem = row.getCell(j);
                            if(ObjectUtil.isNotNull(cellItem)){
                                cellItem.setCellStyle(itemStyle);
                            }else {
                                cell = row.createCell(j);
                                cell.setCellStyle(itemStyle);
                            }
                        }
                    }
                    counter++;
                }
            }
            //浏览器下载
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            response.setHeader("Content-Disposition", "attachment;filename=" + mbImportExcelRecord.getFileName());
            response.flushBuffer();
            workBook.write(response.getOutputStream());
            workBook.close();
        } catch (IOException e) {
            log.error("下载模板异常", e);
        } finally {
            if(httpUrl!=null){
                httpUrl.disconnect();
            }
        }
    }


    @Override
    public void downloadImportExcelOrg(Long recordId, HttpServletResponse response) {
        try {
            MbImportExcelRecord mbImportExcelRecord = this.getRecordById(recordId);
            if(ObjectUtil.isNull(mbImportExcelRecord.getFileUrl())){
                throw new CorpBusinessException(OrganizationResponseCodeEnum.FILE_IS_NULL);
            }
            Example example = new Example(MbImportExcelRecordDetailLog.class);
            example.createCriteria().andEqualTo("recordId", recordId);
            example.setOrderByClause("line_no+0");
            List<MbImportExcelRecordDetailLog>  list = mbImportExcelRecordDetailLogMapper.selectByExample(example);
            // k--> sheetIndex+lineNo, v --> errorList
            Map<String, List<String>> mapMap = new HashMap<>();
            for (int i = 0; i < list.size(); i++) {
                MbImportExcelRecordDetailLog item = list.get(i);
                List<String> errorLogList = new ArrayList<>();
                String key = item.getSheetIndex()+"_"+ item.getLineNo();
                if(mapMap.containsKey(key)){
                    errorLogList =  mapMap.get(key);
                }
                errorLogList.add(item.getMessage());
                mapMap.put(key,errorLogList);
            }



            String ossUrl = basicOssClientLoader.privateGeneratePresignedUrl(mbImportExcelRecord.getFileUrl());

            // 填充excel-error错误信息
            fillOrgExcelWithErrorStyle(response, mbImportExcelRecord, mapMap, ossUrl);
        } catch (Exception e) {
            log.error("searchImportExcelRecordDetailLogByPage", e);
        }

    }


    private void fillOrgExcelWithErrorStyle(HttpServletResponse response, MbImportExcelRecord mbImportExcelRecord, Map<String, List<String>> mapMap, String ossUrl) {
        URL urlfile = null;
        HttpURLConnection httpUrl = null;
        try {
            if (ossUrl == null) {
                throw new CorpBusinessException(OrganizationResponseCodeEnum.FILE_IS_NULL);
            }
            urlfile = new URL(ossUrl);
            if(ObjectUtil.isNotEmpty(basicServerApollo.getProxyUrl())&&ObjectUtil.isNotEmpty(basicServerApollo.getProxyPort())){
                Proxy proxy = new Proxy(Proxy.Type.HTTP, new InetSocketAddress(basicServerApollo.getProxyUrl(), basicServerApollo.getProxyPort()));
                httpUrl = (HttpURLConnection) urlfile.openConnection(proxy);
            }else {
                httpUrl = (HttpURLConnection) urlfile.openConnection();
            }
            httpUrl.connect();
            XSSFWorkbook workBook = new XSSFWorkbook(httpUrl.getInputStream());


            for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
                XSSFSheet sheet = workBook.getSheetAt(i);
                Iterator<Row> rowIterator = sheet.iterator();
                Row row;
                int counter = 0;
                int cellIndex = 0;    // 从多少列添加错误提示
                int errorIndex = 0;   // 从多少行开始错误提示
                //para cada fila de la hoja
                while(rowIterator.hasNext()){
                    row = rowIterator.next();
                    // 取不同sheet 最后一列
                    if(i == 0){
                        cellIndex = sheet.getRow(1).getLastCellNum();
                        errorIndex = 1;
                    }
                    if(i == 1){
                        cellIndex = sheet.getRow(0).getLastCellNum();
                        errorIndex = 0;
                    }
                    if(i == 2){
                        cellIndex = sheet.getRow(0).getLastCellNum();
                        errorIndex = 0;
                    }
                    if(i == 3){
                        cellIndex = sheet.getRow(0).getLastCellNum();
                        errorIndex = 0;
                    }
                    XSSFCellStyle cellStyle = workBook.createCellStyle();
                    //单元格锁定的样式
                    cellStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                    cellStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                    Font font = workBook.createFont();
                    font.setColor(IndexedColors.RED.getIndex());
                    cellStyle.setFont(font);
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    if(counter == errorIndex ){
                        Cell cell = row.createCell(row.getLastCellNum());

                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue("错误提示");
                        cell.setCellStyle(cellStyle);
                    }
                    if(counter > errorIndex  ){
                        String key = i+"_"+(counter+1);
                        List<String> errorLogList =  mapMap.get(key);
                        if(CollectionUtil.isEmpty(errorLogList)){
                            counter++;
                            continue;
                        }
                        String errorLog = errorLogList.stream().collect(Collectors.joining(","));

                        Cell cell = row.createCell(cellIndex-1);
                        cell.setCellValue(errorLog);
                        cell.setCellStyle(cellStyle);

                        XSSFCellStyle itemStyle = workBook.createCellStyle();
                        //单元格锁定的样式人
                        itemStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());
                        itemStyle.setFillBackgroundColor(IndexedColors.GOLD.getIndex());
                        itemStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            Cell cellItem = row.getCell(j);
                            if(ObjectUtil.isNotNull(cellItem)){
                                cellItem.setCellStyle(itemStyle);
                            }else {
                                cell = row.createCell(j);
                                cell.setCellStyle(itemStyle);
                            }
                        }
                    }
                    counter++;
                }
            }
            //浏览器下载
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            response.setHeader("Content-Disposition", "attachment;filename=" + mbImportExcelRecord.getFileName());
            response.flushBuffer();
            workBook.write(response.getOutputStream());
            workBook.close();
        } catch (IOException e) {
            log.error("下载模板异常", e);
        } finally {
            if(httpUrl!=null){
                httpUrl.disconnect();
            }
        }
    }

    private SearchMbImportExcelRecordDetailLogResponseVo convertSearchRecordVo(MbImportExcelRecordDetailLog recordDetailLog) {
        SearchMbImportExcelRecordDetailLogResponseVo searchMbImportExcelRecordDetailLogResponseVo = new SearchMbImportExcelRecordDetailLogResponseVo();
        searchMbImportExcelRecordDetailLogResponseVo.setId(recordDetailLog.getId());
        searchMbImportExcelRecordDetailLogResponseVo.setLineNo(recordDetailLog.getLineNo());
        searchMbImportExcelRecordDetailLogResponseVo.setSheetIndex(recordDetailLog.getSheetIndex());
        searchMbImportExcelRecordDetailLogResponseVo.setSheetName(recordDetailLog.getSheetName());
        searchMbImportExcelRecordDetailLogResponseVo.setMessage(recordDetailLog.getMessage());
        searchMbImportExcelRecordDetailLogResponseVo.setStatusName(MbImportExcelRecordStatusEnum.getStatusName(recordDetailLog.getStatus()));
        return searchMbImportExcelRecordDetailLogResponseVo;
    }

    private SearchMbImportExcelRecordResponseVo convertSearchRecordVo(MbImportExcelRecord record, Map<String, String> nameMap) {
        SearchMbImportExcelRecordResponseVo searchMbImportExcelRecordResponseVo = new SearchMbImportExcelRecordResponseVo();
        searchMbImportExcelRecordResponseVo.setRecordId(record.getId());
        searchMbImportExcelRecordResponseVo.setFileTypeName(MbImportExcelRecordTypeEnum.getTypeName(record.getType()));
        searchMbImportExcelRecordResponseVo.setFileUrl(record.getFileUrl());
        searchMbImportExcelRecordResponseVo.setFileName(record.getFileName());
        searchMbImportExcelRecordResponseVo.setTotalCount(record.getTotalCount() == null ? 0 : record.getTotalCount());
        searchMbImportExcelRecordResponseVo.setFailCount(record.getFailCount() == null ? 0 : record.getFailCount());
        searchMbImportExcelRecordResponseVo.setSuccessCount(record.getCheckPassedCount());
        searchMbImportExcelRecordResponseVo.setStatusName(MbImportExcelRecordStatusEnum.getStatusName(record.getStatus()));
        searchMbImportExcelRecordResponseVo.setUploadTimeStr(DateUtil.dateToString(record.getDatachangeCreatetime(), "yyyy-MM-dd HH:mm:ss"));
        searchMbImportExcelRecordResponseVo.setOperatorUid(record.getUid());
        searchMbImportExcelRecordResponseVo.setOperatorOrgId(record.getOrgId());
        searchMbImportExcelRecordResponseVo.setOperatorName(nameMap.get(record.getUid()));
        return searchMbImportExcelRecordResponseVo;
    }

}
